package springboot.tools.utils;

import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;
import springboot.tools.model.Column;

import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * Excel操作工具类
 * 2018年10月20日
 */
@Slf4j
public class ExcelUtil {

    // 保持在内存的最大数据量，超过将写到磁盘
    private static final int MAX_DATA_COUNT_IN_MEMORY = 500;
    
    public static final int XLSX_FLAG = 0x02;
    
    private static final String SUFFIX_NAME = ".xlsx";

    public static final String DEFAULT_SHEET_PREFIX = "sheet";

    public static final  String UNDER_LINE = "_";

    public static final Integer EXCEL_MAX_SIZE = 50000;

    // 导出认证数据文件名称
    public static final String EXPORT_CERTDATA_FILE_NAME = "kepler_dashboard";

    public static final Integer EXCEL_PAGE_SIZE = 10000;

    
    public static String getFileName (String filePrefixName) {
        StringBuffer sbuff = new StringBuffer();
        sbuff.append(filePrefixName);
        sbuff.append("_");
        sbuff.append(getNowDate());
        sbuff.append(SUFFIX_NAME);
        return sbuff.toString();
    }

    public static String getNowDate() {
        DateFormat df = new SimpleDateFormat("yyyyMMdd");
        return df.format(new Date());
    }

    public static String getSuffixName() {
        return SUFFIX_NAME;
    }

    public static Workbook createWriteWorkbook() {
        return new SXSSFWorkbook(MAX_DATA_COUNT_IN_MEMORY);
    }

    /**
     * 导出数据列表
     *
     * @param outputStream
     * @param dataList
     * @return
     */
    public static boolean writeDataList(OutputStream outputStream,
                                        List<List<String>> dataList,
                                        Workbook workbook,
                                        Sheet sheet) {
        for (int rowIndex = 0; rowIndex < dataList.size(); rowIndex++) {
            Row row = sheet.createRow(rowIndex);
            for (int columnIndex = 0; columnIndex < dataList.get(rowIndex).size(); columnIndex++) {
                Cell cell = row.createCell(columnIndex, CellType.STRING);
                CellStyle cellStyle = workbook.createCellStyle();
                DataFormat format = workbook.createDataFormat();
                cellStyle.setDataFormat(format.getFormat("@"));
                cell.setCellStyle(cellStyle);
                cell.setCellValue(dataList.get(rowIndex).get(columnIndex));
            }
        }
        try {
            workbook.write(outputStream);
            return true;
        } catch (IOException e) {
            log.warn("excelUtil写入数据异常", e);
        }
        return false;
    }

    public static boolean writeDataList(OutputStream outputStream, int excelTypeFlag, List<List<String>> dataList) {
        Workbook workbook = createWriteWorkbook();
        Sheet sheet = workbook.createSheet(DEFAULT_SHEET_PREFIX + 1);
        CellStyle cellStyle = workbook.createCellStyle();
        for (int rowIndex = 0; rowIndex < dataList.size(); rowIndex++) {
            Row row = sheet.createRow(rowIndex);
            for (int columnIndex = 0; columnIndex < dataList.get(rowIndex).size(); columnIndex++) {
                Cell cell = row.createCell(columnIndex, CellType.STRING);
                DataFormat format = workbook.createDataFormat();
                cellStyle.setDataFormat(format.getFormat("@"));
                cell.setCellStyle(cellStyle);
                cell.setCellValue(dataList.get(rowIndex).get(columnIndex));
            }
        }
        try {
            workbook.write(outputStream);
            return true;
        } catch (IOException e) {
            log.warn("excelUtil写入数据异常", e);
        }
        return false;
    }

    /**
     * 分次数导出数据列表，提高效率和性能，减少内存使用
     *
     * @param dataList
     * @param currentExportTimes
     * @param exportCountEveryTime
     * @return
     */
    public static void writeDataList(List<List<String>> dataList,
                                     int currentExportTimes,
                                     int exportCountEveryTime,
                                     Workbook workbook,
                                     Sheet sheet) {
        CellStyle cellStyle = workbook.createCellStyle();
        for (int rowIndex = 0; rowIndex < dataList.size(); rowIndex++) {
            Row row = null;
            if (currentExportTimes > 1) {
                row = sheet.createRow((currentExportTimes - 1) * exportCountEveryTime + rowIndex + 1);
            } else {
                row = sheet.createRow((currentExportTimes - 1) * exportCountEveryTime + rowIndex);
            }
            for (int columnIndex = 0; columnIndex < dataList.get(rowIndex).size(); columnIndex++) {
                Cell cell = row.createCell(columnIndex, CellType.STRING);
                DataFormat format = workbook.createDataFormat();
                cellStyle.setDataFormat(format.getFormat("@"));
                cell.setCellStyle(cellStyle);
                cell.setCellValue(dataList.get(rowIndex).get(columnIndex));
            }
        }
    }

    public static List<List<String>> readExcelDataList(
            InputStream is,
            String suffix) {
        Workbook wb = null;
        List<List<String>> result = new ArrayList<>();
        try {
            if (suffix.toLowerCase().endsWith("xls")) {
                wb = new HSSFWorkbook(is);
            } else if (suffix.toLowerCase().endsWith("xlsx")) {
                wb = new XSSFWorkbook(is);
            }
            // 默认用第一个sheet
            Sheet sheet = wb.getSheetAt(0);
            // 获取行的数量
            int rowNum = sheet.getLastRowNum() + 1;
            for (int i = 1; i < rowNum; i++) {
                List<String> rowList = new ArrayList<>();
                Row row = sheet.getRow(i);
                if (row == null) {
                    continue;
                }
                // 获取单元格的数量
                int cellNum = row.getLastCellNum();
                //如果每一个单元格都为空 则不需要包含
                boolean cellNotEmpty = false;
                for (int j = 0; j < cellNum; j++) {
                    Cell cell = row.getCell(j);
                    if (cell == null) {
                        if (j == 0) {
                            break;
                        }
                        rowList.add("");
                    } else {
                        cell.setCellType(CellType.STRING);
                        String cellValue = cell.getStringCellValue();
                        if (j == 0 && StringUtils.isBlank(cellValue)) {
                            break;
                        }
                        rowList.add(cellValue);
                        cellNotEmpty = true;
                    }
                }
                if (rowList != null && rowList.size() > 0 && cellNotEmpty) {
                    result.add(rowList);
                }
            }
        } catch (Exception e) {
            log.error("readExcelDataList failed.", e);
        }
        return result;
    }

    /**
     * 读取多个sheet的数据
     *
     * @param is
     * @param suffix
     * @return
     */
    public static Map<String, List<List<String>>> readSheetsExcelDataList(InputStream is, String suffix, int line) {
        Workbook wb = null;
        Map<String, List<List<String>>> sheetMapData = Maps.newLinkedHashMap();
        try {
            if (suffix.toLowerCase(Locale.ROOT).endsWith("xls")) {
                wb = new HSSFWorkbook(is);
            } else if (suffix.toLowerCase(Locale.ROOT).endsWith("xlsx")) {
                wb = new XSSFWorkbook(is);
            }

            if (wb != null) {
                FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
                int sheetsNumber = wb.getNumberOfSheets();
                for (int number = 0; number < sheetsNumber; number++) {
                    List<List<String>> result = new ArrayList<>();
                    Sheet sheet = wb.getSheetAt(number);
                    for (Row row : sheet) {
                        if (row == null) {
                            continue;
                        }
                        List<String> itemList = new ArrayList<String>();
                        short lastCellNum = row.getLastCellNum();
                        for (int i = 0; i < lastCellNum; i++) {
                            Cell cell = row.getCell(i);
                            if (cell == null) {
                                itemList.add("");
                                continue;
                            }
                            itemList.add(getCellContent(evaluator, cell));
                        }
                        result.add(itemList);
                    }
                    sheetMapData.put(wb.getSheetName(number), result);
                }
            }
        } catch (Exception e) {
            log.error("readSheetsExcelDataList failed.", e);
        }
        return sheetMapData;
    }


    public static List<List<String>> readExcelDataList(MultipartFile file, int line) {
        Workbook wb = null;
        List<List<String>> result = new ArrayList<>();
        try {
            String fileName = file.getOriginalFilename();
            InputStream fi = file.getInputStream();
            if (fileName.toLowerCase().endsWith("xls")) {
                wb = new HSSFWorkbook(fi);
            } else if (fileName.toLowerCase().endsWith("xlsx")) {
                wb = new XSSFWorkbook(fi);
            }

            Sheet sheet = wb.getSheetAt(0);

            int rowNum = sheet.getLastRowNum() + 1;
            for (int i = line; i < rowNum; i++) {
                List<String> rowList = new ArrayList<>();
                Row row = sheet.getRow(i);
                int cellNum = row.getLastCellNum();
                for (int j = 0; j < cellNum; j++) {
                    Cell cell = row.getCell(j);
                    if (cell == null) {
                        rowList.add("");
                    } else {
                        cell.setCellType(CellType.STRING);
                        String cellValue = null;
                        cellValue = cell.getStringCellValue();
                        if (j == 0 && StringUtils.isBlank(cellValue)) {
                            break;
                        }
                        rowList.add(cellValue);
                    }
                }
                if (rowList != null && !rowList.isEmpty()) {
                    result.add(rowList);
                }
            }
        } catch (IOException e) {
            log.error("read excel error:{}", e);
        }
        return result;
    }

    /**
     * 处理某一单元格为空的情况，防止数据错位
     */
    public static List<List<String>> readDataListWithBlank(InputStream inputStream) {
        List<List<String>> result = new ArrayList<List<String>>();
        try {
            Workbook workbook = WorkbookFactory.create(inputStream);
            Sheet sheet = workbook.getSheetAt(0);
            FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
            for (Row row : sheet) {
                List<String> itemList = new ArrayList<String>();
                short lastCellNum = row.getLastCellNum();
                for (int i = 0; i < lastCellNum; i++) {
                    Cell cell = row.getCell(i);
                    if (cell == null) {
                        itemList.add("");
                        continue;
                    }
                    itemList.add(getCellContent(evaluator, cell));
                }
                result.add(itemList);
            }

        } catch (InvalidFormatException e) {
            log.warn("ExcelUtil解析输入流异常,异常信息:", e);
        } catch (IOException e) {
            log.warn("ExcelUtil解析输入流异常,异常信息:", e);
        }
        return result;
    }

    private static String getCellContent(FormulaEvaluator evaluator, Cell cell) {
        CellType cellType = cell.getCellTypeEnum();
        if (cellType.equals(CellType.STRING)) {
            return cell.getStringCellValue();
        } else if (cellType.equals(CellType.NUMERIC)) {
            //处理对于单元格格式为数值类型的情况，包括千分符,-的去除
            DataFormatter df = new DataFormatter();
            String value = df.formatCellValue(cell).replaceAll(",", "")
                    .replaceAll(" ", "").replaceAll("=", "");
            return value;
        } else if (cellType.equals(CellType.BOOLEAN)) {
            return new Boolean(cell.getBooleanCellValue()).toString();
        } else if (cellType.equals(CellType.FORMULA)) {
            CellValue cellValue = evaluator.evaluate(cell);
            cellType = cellValue.getCellTypeEnum();
            if (cellType.equals(CellType.STRING)) {
                return cellValue.getStringValue();
            } else if (cellType.equals(CellType.NUMERIC)) {
                return new Double(cellValue.getNumberValue()).toString();
            } else if (cellType.equals(CellType.BOOLEAN)) {
                return new Boolean(cellValue.getBooleanValue()).toString();
            } else {
                return "";
            }
        } else {
            return "";
        }
    }

    public static CellStyle getCellStyle(Workbook workbook) {
        return getCellStyle(workbook, (short) 9, true, true, false);
    }

    public static CellStyle getCellStyle(Workbook workbook, short fontSize) {
        return getCellStyle(workbook, fontSize, true, true, true);
    }

    public static CellStyle getCellStyle(Workbook workbook, short fontSize, boolean isBorder) {
        return getCellStyle(workbook, fontSize, isBorder, true, false);
    }

    public static CellStyle getCellStyle(Workbook workbook, short fontSize, boolean isBorder, boolean isBold) {
        return getCellStyle(workbook, fontSize, isBorder, true, isBold);
    }

    public static CellStyle getCellStyle(Workbook workbook, short fontSize, boolean isBorder, boolean isWrapText, boolean isBold) {
        CellStyle titleStyle = workbook.createCellStyle();
        Font boldFont = workbook.createFont();
        boldFont.setFontHeightInPoints(fontSize);// 字体大小
        boldFont.setBold(isBold);// 这是字体加粗
        boldFont.setFontName("微软雅黑");// 设置字体的样式
        titleStyle.setFont(boldFont);
        if (isBorder) {
            titleStyle.setBorderLeft(BorderStyle.THIN);
            titleStyle.setBorderBottom(BorderStyle.THIN);
            titleStyle.setBorderRight(BorderStyle.THIN);
            titleStyle.setBorderTop(BorderStyle.THIN);
        }
        titleStyle.setAlignment(HorizontalAlignment.CENTER);
        titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        if (isWrapText) {
            titleStyle.setWrapText(true);
        }
        return titleStyle;
    }

    /**
     * 创建表头 支持单元格合并 支持自适应列宽
     *
     * @param columnList
     * @param sheet
     * @param titleStyle
     */
    public static void createHead(List<Column> columnList, Sheet sheet, CellStyle titleStyle) {
        for (Column column : columnList) {
            int r = column.getRow();
            int rLen = column.getrLen();
            int c = column.getCol();
            int cLen = column.getcLen();
            int endR = r + rLen;
            int endC = c + cLen;
            if (endC > c) {
                endC--;
            }

            Row row = sheet.getRow(r);
            if (row == null) {
                row = sheet.createRow(r);
            }
            Cell cell = row.getCell(c);
            if (null == cell) {
                cell = row.createCell(c);
            }
            //设置表头样式
            if (column.getCellStyle() != null) {
                cell.setCellStyle(column.getCellStyle());
            } else {
                cell.setCellStyle(titleStyle);
            }
            cell.setCellValue(column.getContent());

            CellRangeAddress cra = new CellRangeAddress(r, endR, c, endC);
            if (cra.getNumberOfCells() > 1) {
                sheet.addMergedRegion(cra);
                if (column.isCellRangeBorder()) {
                    RegionUtil.setBorderBottom(BorderStyle.THIN, cra, sheet); // 下边框
                    RegionUtil.setBorderLeft(BorderStyle.THIN, cra, sheet); // 左边框
                    RegionUtil.setBorderRight(BorderStyle.THIN, cra, sheet); // 右边框
                    RegionUtil.setBorderTop(BorderStyle.THIN, cra, sheet); // 上边框
                }
            }
            if (sheet instanceof SXSSFSheet) {
                ((SXSSFSheet) sheet).trackAllColumnsForAutoSizing();
            }
            sheet.autoSizeColumn(c);
            autoSizeColumn(sheet, cell, c);
            if (CollectionUtils.isNotEmpty(column.getListColumn())) {
                createHead(column.getListColumn(), sheet, titleStyle);
            }
        }
    }

    public static void autoSizeColumn(Sheet sheet, Cell cell, int cellIndex) {
        //自适应中文宽度
        if (cell.getCellTypeEnum().equals(CellType.STRING)) {
            //认为英文和数字都占用两个字节 故 * 2
            int length = cell.getStringCellValue().length() * 2 + 1;
            int columnWidth = sheet.getColumnWidth(cellIndex) / 256;
            if (columnWidth < length) {
                //对string列调整列宽
                sheet.setColumnWidth(cellIndex, length * 256);
            }
        }
    }

    /**
     * 处理column  特别是合并单元格
     * @param titleListInfo
     * @param startRow
     * @param startCol
     * @return
     */
    public static List<Column> createTitleColumn(List<Object> titleListInfo, int startRow, int startCol) {
        List<Column> columnList = Lists.newArrayList();
        for (Object titleObject : titleListInfo) {
            if (titleObject instanceof String) {
                columnList.add(new Column((String) titleObject, startRow, startCol, 0, 0));
                startCol++;
            } else if (titleObject instanceof List) {
                List<Object> titleList = (List) titleObject;
                columnList.addAll(createTitleColumn(titleList, startRow, startCol));
                startRow++;
            } else if (titleObject instanceof Map) {
                Map<String, List<String>> titleMap = (Map) titleObject;
                for (Map.Entry<String, List<String>> t : titleMap.entrySet()) {
                    ArrayList<Object> childValue = Lists.newArrayList(t.getValue());
                    Column column = new Column(t.getKey(), startRow, startCol, 0, childValue.size());
                    columnList.add(column);
                    startRow++;
                    List<Column> listColumn = createTitleColumn(childValue, startRow, startCol);
                    startCol += listColumn.size();
                    column.setListColumn(listColumn);
                    startRow--;
                }
            }
        }
        return columnList;
    }
}
